<?php

class Ekstrak extends Controller {
	function __construct()
	{
		parent::Controller();
		$this->load->helper('bantuan_helper');
		$this->load->model('Ekstrak_model');
		$this->load->helper('file');
		$this->load->helper(array('form', 'url'));
		//
		if(!$this->session->userdata('user_id') && !$this->session->userdata('user_display_name') ):
			redirect('backend');
		elseif($this->session->userdata('user_rule')=='Sales'):
			redirect('backend/no_acces');
		endif;

	}

	function index()
	{
		$this->load->library('pagination');
		$offset = $this->uri->segment(4);
		$config['base_url'] = site_url().'/backend/ekstrak/index';
		$config['total_rows'] = count($this->Ekstrak_model->getLog(FALSE,FALSE));
		$config['per_page'] = 10;
		$config['uri_segment'] = '4';
		
		$data['urut'] = $this->uri->segment(4);
		$data['title'] = 'Manage Datawarehouse';
		$data['template'] = 'ekstrak/index';
		$data['res'] = $this->Ekstrak_model->getLog($config['per_page'],$offset);
		$this->pagination->initialize($config);
		$this->load->view('backend/index',$data);
	}
	
	function advanced()
	{
		$this->load->library('pagination');
		$offset = $this->uri->segment(4);
		$config['base_url'] = site_url().'/backend/ekstrak/advanced';
		$config['total_rows'] = count($this->Ekstrak_model->getLog(FALSE,FALSE));
		$config['per_page'] = 10;
		$config['uri_segment'] = '4';
		
		$data['urut'] = $this->uri->segment(4);
		$data['title'] = 'Manage Datawarehouse';
		$data['template'] = 'ekstrak/advanced';
		$data['res'] = $this->Ekstrak_model->getLog($config['per_page'],$offset);
		$this->pagination->initialize($config);
		$this->load->view('backend/index',$data);
	}
	
	function clean()
	{
		$dim = $this->uri->segment(4);
		$user = $this->session->userdata('user_display_name');
		$date = date("Y-m-d h:i:s");
		
		if ($dim == 'bank')
		{
			$cek_row = $this->db->get('dim_bank')->num_rows();
			if ($cek_row > 0):
				$data = array(
							'log_user'=>$user,
							'log_desc'=>'Clean dim_bank table sebanyak '.$cek_row.' rows',
							'log_date'=>$date
							);
				$this->Ekstrak_model->addLog($data);
				$this->Ekstrak_model->cleanTable('dim_bank');
				$this->session->set_flashdata('message_type','<div id="kotak" class="success">Tabel dim_bank berhasil dibersihkan</div>');
				redirect('backend/ekstrak/advanced');
			else:
				$this->session->set_flashdata('message_type','<div id="kotak" class="error">Tabel dim_bank dalam keadaan kosong</div>');
				redirect('backend/ekstrak/advanced');
			endif;
		}
			
		elseif($dim == 'produk')
		{
			$cek_row = $this->db->get('dim_produk')->num_rows();
			if ($cek_row > 0):
				$data = array(
							'log_user'=>$user,
							'log_desc'=>'Clean dim_produk table sebanyak '.$cek_row.' rows',
							'log_date'=>$date
							);
				$this->Ekstrak_model->addLog($data);
				$this->Ekstrak_model->cleanTable('dim_produk');
				$this->session->set_flashdata('message_type','<div id="kotak" class="success">Tabel dim_produk berhasil dibersihkan</div>');
				redirect('backend/ekstrak/advanced');
			else:
				$this->session->set_flashdata('message_type','<div id="kotak" class="error">Tabel dim_produk dalam keadaan kosong</div>');
				redirect('backend/ekstrak/advanced');
			endif;
		}
		elseif($dim == 'member')
		{
			$cek_row = $this->db->get('dim_member')->num_rows();
			if ($cek_row > 0):
				$data = array(
							'log_user'=>$user,
							'log_desc'=>'Clean dim_member table sebanyak '.$cek_row.' rows',
							'log_date'=>$date
							);
				$this->Ekstrak_model->addLog($data);
				$this->Ekstrak_model->cleanTable('dim_member');
				$this->session->set_flashdata('message_type','<div id="kotak" class="success">Tabel dim_member berhasil dibersihkan</div>');
				redirect('backend/ekstrak/advanced');
			else:
				$this->session->set_flashdata('message_type','<div id="kotak" class="error">Tabel dim_member dalam keadaan kosong</div>');
				redirect('backend/ekstrak/advanced');
			endif;
		}
		elseif($dim == 'lokasi')
		{
			$cek_row = $this->db->get('dim_lokasi')->num_rows();
			if ($cek_row > 0):
				$data = array(
							'log_user'=>$user,
							'log_desc'=>'Clean dim_lokasi table sebanyak '.$cek_row.' rows',
							'log_date'=>$date
							);
				$this->Ekstrak_model->addLog($data);
				$this->Ekstrak_model->cleanTable('dim_lokasi');
				$this->session->set_flashdata('message_type','<div id="kotak" class="success">Tabel dim_lokasi berhasil dibersihkan</div>');
				redirect('backend/ekstrak/advanced');
			else:
				$this->session->set_flashdata('message_type','<div id="kotak" class="error">Tabel dim_lokasi dalam keadaan kosong</div>');
				redirect('backend/ekstrak/advanced');
			endif;
		}
		elseif($dim == 'time')
		{
		
			$cek_row = $this->db->get('dim_time')->num_rows();
			if ($cek_row > 0):
				$data = array(
							'log_user'=>$user,
							'log_desc'=>'Clean dim_time table sebanyak '.$cek_row.' rows',
							'log_date'=>$date
							);
				$this->Ekstrak_model->addLog($data);
				$this->Ekstrak_model->cleanTable('dim_time');
				$this->session->set_flashdata('message_type','<div id="kotak" class="success">Tabel dim_time berhasil dibersihkan</div>');
				redirect('backend/ekstrak/advanced');
			else:
				$this->session->set_flashdata('message_type','<div id="kotak" class="error">Tabel dim_time dalam keadaan kosong</div>');
				redirect('backend/ekstrak/advanced');
			endif;
		
		}
		elseif($dim == 'sales')
		{
		
			$cek_row = $this->db->get('fact_sales')->num_rows();
			if ($cek_row > 0):
				$data = array(
							'log_user'=>$user,
							'log_desc'=>'Clean fact_sales table sebanyak '.$cek_row.' rows',
							'log_date'=>$date
							);
				$this->Ekstrak_model->addLog($data);
				$this->Ekstrak_model->cleanTable('fact_sales');
				$this->session->set_flashdata('message_type','<div id="kotak" class="success">Tabel fact_sales berhasil dibersihkan</div>');
				redirect('backend/ekstrak/advanced');
			else:
				$this->session->set_flashdata('message_type','<div id="kotak" class="error">Tabel fact_sales dalam keadaan kosong</div>');
				redirect('backend/ekstrak/advanced');
			endif;
		
		}
		elseif($this->uri->segment(4) == 'log')
		{
			$this->Ekstrak_model->cleanTableLog('tb_log');
			redirect('backend/ekstrak/advanced');
		}

				
	}
	
	function load()
	{
		$dim = $this->uri->segment(4);
		$user = $this->session->userdata('user_display_name');
		$date = date("Y-m-d h:i:s");
		
		if ($dim == 'bank')
		{
			$cek_row = $this->db->get('dim_bank')->num_rows();
			if ($cek_row == 0):
				// select tabel bank dari db OLTP
				$default_db ='';
				$this->default_db = $this->load->database('default', TRUE);// extrem change db ke default db nya dbdemankcomp
				$query = $this->default_db->get('tb_bank');
				$res = $query->result_array();
				foreach ($res as $row):
					$data = array(
							'sk_bank'=>$row['bank_id'],
							'kode_bank'=>$row['bank_id'],
							'nama_bank'=>$row['nama_bank'],
							);
					$this->load->database('db_datawarehouse', TRUE); // extrem change db ke second_db nya dbdemankcomp_dw di loop
					$this->Ekstrak_model->loadTable('dim_bank',$data);
					$this->load->database('default', TRUE); // extrem change db balik ke db default and ikut di loop
				endforeach;
				$this->load->database('db_datawarehouse', TRUE); // extrem change db ke second_db nya dbdemankcomp_dw
				$jml_rows = $this->db->get('dim_bank')->num_rows(); // kasih jumlah rows nya
				$data = array(
							'log_user'=>$user,
							'log_desc'=>'Load dim_bank table sebanyak '.$jml_rows.' rows',
							'log_date'=>$date
							);
				$this->Ekstrak_model->addLog($data);
				
				$this->session->set_flashdata('message_type','<div id="kotak" class="success">Tabel dim_bank berhasil di Load</div>');
				redirect('backend/ekstrak/advanced');
			else:
				$this->session->set_flashdata('message_type','<div id="kotak" class="error">Tabel dim_bank harus di bersihkan (clean) dahulu</div>');
				redirect('backend/ekstrak/advanced');
			endif;
		}
		elseif($dim == 'produk')
		{
			$cek_row = $this->db->get('dim_produk')->num_rows();
			if ($cek_row == 0):
				// select tabel bank dari db OLTP
				$default_db ='';
				$this->default_db = $this->load->database('default', TRUE);// extrem change db ke default db nya dbdemankcomp
				$query = $this->default_db->query(
					'SELECT p.produk_id,p.prod_kat_id,k.nama_kategori,p.prod_vendor_id,v.nama_vendor,p.produk_id,p.nama_produk
					FROM tb_produk p
					JOIN tb_kat_produk k ON p.prod_kat_id = k.kategori_id
					JOIN tb_vendor v ON p.prod_vendor_id = v.vendor_id');
				$res = $query->result_array();
				foreach ($res as $row):
					$data = array(
							'sk_produk'=>$row['produk_id'],
							'kode_kategori'=>$row['prod_kat_id'],
							'nama_kategori'=>$row['nama_kategori'],
							'kode_vendor'=>$row['prod_vendor_id'],
							'nama_vendor'=>$row['nama_vendor'],
							'kode_produk'=>$row['produk_id'],
							'nama_produk'=>$row['nama_produk'],
							);
					$this->load->database('db_datawarehouse', TRUE); // extrem change db ke second_db nya dbdemankcomp_dw di loop
					$this->Ekstrak_model->loadTable('dim_produk',$data);
					$this->load->database('default', TRUE); // extrem change db balik ke db default and ikut di loop
				endforeach;
				$this->load->database('db_datawarehouse', TRUE); // extrem change db ke second_db nya dbdemankcomp_dw
				$jml_rows = $this->db->get('dim_produk')->num_rows(); // kasih jumlah rows nya
				$data = array(
							'log_user'=>$user,
							'log_desc'=>'Load dim_produk table sebanyak '.$jml_rows.' rows',
							'log_date'=>$date
							);
				$this->Ekstrak_model->addLog($data);
				
				$this->session->set_flashdata('message_type','<div id="kotak" class="success">Tabel dim_produk berhasil di Load</div>');
				redirect('backend/ekstrak/advanced');
			else:
				$this->session->set_flashdata('message_type','<div id="kotak" class="error">Tabel dim_produk harus di bersihkan (clean) dahulu</div>');
				redirect('backend/ekstrak/advanced');
			endif;
		}
		elseif($dim == 'member')
		{
			$cek_row = $this->db->get('dim_member')->num_rows();
			if ($cek_row == 0):
				// select tabel bank dari db OLTP
				$default_db ='';
				$this->default_db = $this->load->database('default', TRUE);// extrem change db ke default db nya dbdemankcomp
				$query = $this->default_db->query('
				SELECT member_id,mem_kab_id,jenkel,nama
				FROM tb_member 
					');
				$res = $query->result_array();
				foreach ($res as $row):
					if ($row['jenkel'] == 'Laki-laki'):
						$kel = 'L';
					elseif($row['jenkel'] == 'Perempuan'):
						$kel = 'P';
					endif;
					$data = array(
							'sk_member'=>$row['member_id'],
							'jenis_kelamin'=>$kel,
							'jenis_kelamin_desc'=>$row['jenkel'],
							'nama'=>$row['nama'],
							'kode_member'=>$row['member_id']
							);
					$this->load->database('db_datawarehouse', TRUE); // extrem change db ke second_db nya dbdemankcomp_dw di loop
					$this->Ekstrak_model->loadTable('dim_member',$data);
					$this->load->database('default', TRUE); // extrem change db balik ke db default and ikut di loop
				endforeach;
				$this->load->database('db_datawarehouse', TRUE); // extrem change db ke second_db nya dbdemankcomp_dw
				$jml_rows = $this->db->get('dim_member')->num_rows(); // kasih jumlah rows nya
				$data = array(
							'log_user'=>$user,
							'log_desc'=>'Load dim_member table sebanyak '.$jml_rows.' rows',
							'log_date'=>$date
							);
				$this->Ekstrak_model->addLog($data);
				
				$this->session->set_flashdata('message_type','<div id="kotak" class="success">Tabel dim_member berhasil di Load</div>');
				redirect('backend/ekstrak/advanced');
			else:
				$this->session->set_flashdata('message_type','<div id="kotak" class="error">Tabel dim_member harus di bersihkan (clean) dahulu</div>');
				redirect('backend/ekstrak/advanced');
			endif;
		}
		elseif($dim == 'lokasi')
		{
			$cek_row = $this->db->get('dim_lokasi')->num_rows();
			if ($cek_row == 0):
				// select tabel bank dari db OLTP
				$default_db ='';
				$this->default_db = $this->load->database('default', TRUE);// extrem change db ke default db nya dbdemankcomp
				$query = $this->default_db->query(
					'SELECT k.kab_id,p.province_id,k.kab_name,k.kab_prov_id,p.province_name
					FROM tb_province p
					JOIN tb_kab k ON k.kab_prov_id = p.province_id
					ORDER BY k.kab_id ASC
					');
				$res = $query->result_array();
				foreach ($res as $row):
					$data = array(
							'sk_lokasi'=>$row['kab_id'],
							'kode_propinsi'=>$row['province_id'],
							'nama_propinsi'=>$row['province_name'],
							'kode_kab'=>$row['kab_id'],
							'nama_kab'=>$row['kab_name']
							);
					$this->load->database('db_datawarehouse', TRUE); // extrem change db ke second_db nya dbdemankcomp_dw di loop
					$this->Ekstrak_model->loadTable('dim_lokasi',$data);
					$this->load->database('default', TRUE); // extrem change db balik ke db default and ikut di loop
				endforeach;
				$this->load->database('db_datawarehouse', TRUE); // extrem change db ke second_db nya dbdemankcomp_dw
				$jml_rows = $this->db->get('dim_lokasi')->num_rows(); // kasih jumlah rows nya
				$data = array(
							'log_user'=>$user,
							'log_desc'=>'Load dim_lokasi table sebanyak '.$jml_rows.' rows',
							'log_date'=>$date
							);
				$this->Ekstrak_model->addLog($data);
				
				$this->session->set_flashdata('message_type','<div id="kotak" class="success">Tabel dim_lokasi berhasil di Load</div>');
				redirect('backend/ekstrak/advanced');
			else:
				$this->session->set_flashdata('message_type','<div id="kotak" class="error">Tabel dim_lokasi harus di bersihkan (clean) dahulu</div>');
				redirect('backend/ekstrak/advanced');
			endif;
		}
		elseif($dim == 'time')
		{ 
			$th = $this->input->post('tahun');
			
			if($th == 'all')
			{
				$cek_row = $this->db->get('dim_time')->num_rows();
				if ($cek_row == 0)
				{
					$default_db ='';
					$this->default_db = $this->load->database('default', TRUE);// extrem change db ke default db nya dbdemankcomp
					$query = $this->default_db->query('SELECT * FROM tb_time');
					$res = $query->result_array(); 	 	 	 	 	
					foreach ($res as $row):
						$data = array(
						'date_sk'=>$row['time_id'],
						'month'=>$row['month'],
						'day'=>$row['day'],
						'year'=>$row['year'],
						'date'=>$row['date'],
						'quarter'=>$row['quarter']
						);
						$this->load->database('db_datawarehouse', TRUE); // extrem change db ke second_db nya dbdemankcomp_dw di loop
						$this->Ekstrak_model->loadTable('dim_time',$data);
						$this->load->database('default', TRUE); // extrem change db balik ke db default and ikut di loop
					endforeach;
					
					$this->load->database('db_datawarehouse', TRUE); // extrem change db ke second_db nya dbdemankcomp_dw
					$jml_rows = $this->db->get('dim_time')->num_rows(); // kasih jumlah rows nya
					$data = array(
								'log_user'=>$user,
								'log_desc'=>'Load dim_lokasi table sebanyak '.$jml_rows.' rows',
								'log_date'=>$date
								);
					$this->Ekstrak_model->addLog($data);
					$this->session->set_flashdata('message_type','<div id="kotak" class="success">Tabel dim_lokasi berhasil di Load</div>');
					redirect('backend/ekstrak/advanced');
					
				}
				else
				{
					$this->session->set_flashdata('message_type','<div id="kotak" class="error">Tabel dim_time harus di bersihkan (clean) dahulu</div>');
					redirect('backend/ekstrak/advanced');
				}
			}
			else
			{
				$cek_row = $this->db->get('dim_time')->num_rows();
				if ($cek_row == 0)
				{
					$default_db ='';
					$this->default_db = $this->load->database('default', TRUE);// extrem change db ke default db nya dbdemankcomp
					$query = $this->default_db->query('SELECT * FROM tb_time WHERE year ='.$th);
					$res = $query->result_array(); 	 	 	 	 	
					foreach ($res as $row):
						$data = array(
						'date_sk'=>$row['time_id'],
						'month'=>$row['month'],
						'day'=>$row['day'],
						'year'=>$row['year'],
						'date'=>$row['date'],
						'quarter'=>$row['quarter']
						);
						$this->load->database('db_datawarehouse', TRUE); // extrem change db ke second_db nya dbdemankcomp_dw di loop
						$this->Ekstrak_model->loadTable('dim_time',$data);
						$this->load->database('default', TRUE); // extrem change db balik ke db default and ikut di loop
					endforeach;
					
					$this->load->database('db_datawarehouse', TRUE); // extrem change db ke second_db nya dbdemankcomp_dw
					$jml_rows = $this->db->get('dim_time')->num_rows(); // kasih jumlah rows nya
					$data = array(
								'log_user'=>$user,
								'log_desc'=>'Load dim_time (th '.$th.') table sebanyak '.$jml_rows.' rows',
								'log_date'=>$date
								);
					$this->Ekstrak_model->addLog($data);
					$this->session->set_flashdata('message_type','<div id="kotak" class="success">Tabel dim_lokasi berhasil di Load</div>');
					redirect('backend/ekstrak/advanced');
					
				}
				else
				{
					$this->session->set_flashdata('message_type','<div id="kotak" class="error">Tabel dim_time harus di bersihkan (clean) dahulu</div>');
					redirect('backend/ekstrak/advanced');
				}
			
			}
			
		}
		elseif($dim == 'sales')
		{
			$th = $this->input->post('tahun');
			
			if($th == 'all')
			{
				$cek_time = $this->db->get('dim_time')->num_rows();	
				if ($cek_time != 0):
					$cek_row = $this->db->get('fact_sales')->num_rows();
					if ($cek_row == 0):
						// for fact_sales
						$this->default_db = $this->load->database('default', TRUE);// extrem change db ke default db nya dbdemankcomp
						$query = $this->default_db->query(
						'SELECT td.produk_id,o.order_kab_id,o.order_date,o.date_sk,o.order_member_id,o.order_bank_id,td.det_jml,p.harga_dist,p.harga_jual,p.diskon,td.harga,td.det_subtotal
										FROM tb_order_detail td
										LEFT OUTER JOIN tb_produk p ON p.produk_id = td.produk_id
										LEFT OUTER JOIN tb_order o ON o.order_id = td.order_id
										LEFT OUTER JOIN tb_member m ON m.member_id = o.order_member_id
										WHERE o.order_status = "closed"
										GROUP BY td.order_detail_id
										ORDER BY  td.order_detail_id ASC
										');
						$res = $query->result_array();
						foreach ($res as $row):
							$total_dist = ($row['det_jml'] * $row['harga_dist']);
							$diskon = (($row['diskon'] * $row['harga_jual']/100)*$row['det_jml']);
							$y = explode('-',$row['order_date']);
							$year = $y[0];
							$this->load->database('db_datawarehouse', TRUE); // extrem change db ke second_db nya dbdemankcomp_dw di loop
									$data = array(
									'sk_produk'=>$row['produk_id'],
									'sk_lokasi'=>$row['order_kab_id'],
									'sk_member'=>$row['order_member_id'],
									'sk_bank'=>$row['order_bank_id'],
									'date_sk'=>$row['date_sk'],
									'year'=>$year,
									'jumlah_pembelian'=>$row['det_jml'],
									'harga_produk'=>$row['harga'],
									'total_distributor'=>$total_dist,
									'diskon'=>$diskon,
									'total_kotor'=>$row['det_subtotal'],
									);
									$this->load->database('db_datawarehouse', TRUE); // extrem change db ke second_db nya dbdemankcomp_dw di loop
									$this->Ekstrak_model->loadTable('fact_sales',$data);
									$this->load->database('default', TRUE); // extrem change db balik ke db default and ikut di loop
						endforeach;
						$this->load->database('db_datawarehouse', TRUE); // extrem change db ke second_db nya dbdemankcomp_dw

						$jml_rows = $this->db->get('fact_sales')->num_rows(); // kasih jumlah rows nya
						$data = array(
									'log_user'=>$user,
									'log_desc'=>'Load fact_sales table sebanyak '.$jml_rows.' rows',
									'log_date'=>$date
									);
						$this->Ekstrak_model->addLog($data);
						
						$this->session->set_flashdata('message_type','<div id="kotak" class="success">Tabel fact_sales berhasil di Load</div>');
						redirect('backend/ekstrak/advanced');
					else:
						$this->session->set_flashdata('message_type','<div id="kotak" class="error">Tabel fact_sales harus di bersihkan (clean) dahulu</div>');
						redirect('backend/ekstrak/advanced');
					endif;
				else:
					$this->session->set_flashdata('message_type','<div id="kotak" class="error">Tabel Dimensi Time (dim_time) harus di load dahulu</div>');
					redirect('backend/ekstrak/advanced');
				endif;
			}
			else
			{
				$cek_time = $this->db->get('dim_time')->num_rows();	
				if ($cek_time != 0):
					$cek_row = $this->db->get('fact_sales')->num_rows();
					if ($cek_row == 0):
						// for fact_sales
						$this->default_db = $this->load->database('default', TRUE);// extrem change db ke default db nya dbdemankcomp
						$query = $this->default_db->query(
						'SELECT td.produk_id,o.order_kab_id,o.order_date,o.date_sk,o.order_member_id,o.order_bank_id,td.det_jml,p.harga_dist,p.harga_jual,p.diskon,td.harga,td.det_subtotal
										FROM tb_order_detail td
										LEFT OUTER JOIN tb_produk p ON p.produk_id = td.produk_id
										LEFT OUTER JOIN tb_order o ON o.order_id = td.order_id
										LEFT OUTER JOIN tb_member m ON m.member_id = o.order_member_id
										JOIN tb_time tm on tm.time_id = o.date_sk
										WHERE o.order_status = "closed" AND tm.year = '.$th.'
										GROUP BY td.order_detail_id
										ORDER BY  td.order_detail_id ASC
										');
						$res = $query->result_array();
						foreach ($res as $row):
							$total_dist = ($row['det_jml'] * $row['harga_dist']);
							$diskon = (($row['diskon'] * $row['harga_jual']/100)*$row['det_jml']);
							$y = explode('-',$row['order_date']);
							$year = $y[0];
							$this->load->database('db_datawarehouse', TRUE); // extrem change db ke second_db nya dbdemankcomp_dw di loop
									$data = array(
									'sk_produk'=>$row['produk_id'],
									'sk_lokasi'=>$row['order_kab_id'],
									'sk_member'=>$row['order_member_id'],
									'sk_bank'=>$row['order_bank_id'],
									'date_sk'=>$row['date_sk'],
									'year'=>$year,
									'jumlah_pembelian'=>$row['det_jml'],
									'harga_produk'=>$row['harga'],
									'total_distributor'=>$total_dist,
									'diskon'=>$diskon,
									'total_kotor'=>$row['det_subtotal'],
									);
									$this->load->database('db_datawarehouse', TRUE); // extrem change db ke second_db nya dbdemankcomp_dw di loop
									$this->Ekstrak_model->loadTable('fact_sales',$data);
									$this->load->database('default', TRUE); // extrem change db balik ke db default and ikut di loop
						endforeach;
						$this->load->database('db_datawarehouse', TRUE); // extrem change db ke second_db nya dbdemankcomp_dw

						$jml_rows = $this->db->get('fact_sales')->num_rows(); // kasih jumlah rows nya
						$data = array(
									'log_user'=>$user,
									'log_desc'=>'Load fact_sales (th'.$th.') table sebanyak '.$jml_rows.' rows',
									'log_date'=>$date
									);
						$this->Ekstrak_model->addLog($data);
						
						$this->session->set_flashdata('message_type','<div id="kotak" class="success">Tabel fact_sales berhasil di Load</div>');
						redirect('backend/ekstrak/advanced');
					else:
						$this->session->set_flashdata('message_type','<div id="kotak" class="error">Tabel fact_sales harus di bersihkan (clean) dahulu</div>');
						redirect('backend/ekstrak/advanced');
					endif;
				else:
					$this->session->set_flashdata('message_type','<div id="kotak" class="error">Tabel Dimensi Time (dim_time) harus di load dahulu</div>');
					redirect('backend/ekstrak/advanced');
				endif;			
			}
		}
				
	}
	
	function load_all()
	{
		
		$th = $this->input->post('tahun');
		
		if ($th == 'all')
		{
			$this->Ekstrak_model->cleanTable('dim_bank');
			$this->Ekstrak_model->cleanTable('dim_lokasi');
			$this->Ekstrak_model->cleanTable('dim_time');
			$this->Ekstrak_model->cleanTable('dim_member');
			$this->Ekstrak_model->cleanTable('dim_produk');
			$this->Ekstrak_model->cleanTable('fact_sales');
			
			// for dim_bank
			$default_db ='';
			$this->default_db = $this->load->database('default', TRUE);// extrem change db ke default db nya dbdemankcomp
			$query = $this->default_db->get('tb_bank');
			$res = $query->result_array();
			foreach ($res as $row):
				$data = array(
				'sk_bank'=>$row['bank_id'],
				'kode_bank'=>$row['bank_id'],
				'nama_bank'=>$row['nama_bank'],
				);
				$this->load->database('db_datawarehouse', TRUE); // extrem change db ke second_db nya dbdemankcomp_dw di loop
				$this->Ekstrak_model->loadTable('dim_bank',$data);
				$this->load->database('default', TRUE); // extrem change db balik ke db default and ikut di loop
			endforeach;
			$this->load->database('db_datawarehouse', TRUE); // extrem change db ke second_db nya dbdemankcomp_dw
			
			
			// for dim_produk
			$this->default_db = $this->load->database('default', TRUE);// extrem change db ke default db nya dbdemankcomp
			$query = $this->default_db->query(
			'SELECT p.produk_id,p.prod_kat_id,k.nama_kategori,p.prod_vendor_id,v.nama_vendor,p.produk_id,p.nama_produk
						FROM tb_produk p
						JOIN tb_kat_produk k ON p.prod_kat_id = k.kategori_id
						JOIN tb_vendor v ON p.prod_vendor_id = v.vendor_id');
			$res = $query->result_array();
			foreach ($res as $row):
				$data = array(
				'sk_produk'=>$row['produk_id'],
				'kode_kategori'=>$row['prod_kat_id'],
				'nama_kategori'=>$row['nama_kategori'],
				'kode_vendor'=>$row['prod_vendor_id'],
				'nama_vendor'=>$row['nama_vendor'],
				'kode_produk'=>$row['produk_id'],
				'nama_produk'=>$row['nama_produk'],
				);
				$this->load->database('db_datawarehouse', TRUE); // extrem change db ke second_db nya dbdemankcomp_dw di loop
				$this->Ekstrak_model->loadTable('dim_produk',$data);
				$this->load->database('default', TRUE); // extrem change db balik ke db default and ikut di loop
			endforeach;
			$this->load->database('db_datawarehouse', TRUE); // extrem change db ke second_db nya dbdemankcomp_dw
			
			//for dim_lokasi
			
			$this->default_db = $this->load->database('default', TRUE);// extrem change db ke default db nya dbdemankcomp
			$query = $this->default_db->query(
			'SELECT k.kab_id,p.province_id,k.kab_name,k.kab_prov_id,p.province_name
						FROM tb_province p
						JOIN tb_kab k ON k.kab_prov_id = p.province_id
						ORDER BY k.kab_id ASC
						');
			$res = $query->result_array();
			foreach ($res as $row):
				$data = array(
				'sk_lokasi'=>$row['kab_id'],
				'kode_propinsi'=>$row['province_id'],
				'nama_propinsi'=>$row['province_name'],
				'kode_kab'=>$row['kab_id'],
				'nama_kab'=>$row['kab_name']
				);
				$this->load->database('db_datawarehouse', TRUE); // extrem change db ke second_db nya dbdemankcomp_dw di loop
				$this->Ekstrak_model->loadTable('dim_lokasi',$data);
				$this->load->database('default', TRUE); // extrem change db balik ke db default and ikut di loop
			endforeach;
			$this->load->database('db_datawarehouse', TRUE); // extrem change db ke second_db nya dbdemankcomp_dw
			
			// for dim_member
			
			$this->default_db = $this->load->database('default', TRUE);// extrem change db ke default db nya dbdemankcomp
			$query = $this->default_db->query('
					SELECT member_id,mem_kab_id,jenkel,nama
					FROM tb_member 
						');
			$res = $query->result_array();
			foreach ($res as $row):
				if ($row['jenkel'] == 'Laki-laki'):
					$kel = 'L';
				elseif($row['jenkel'] == 'Perempuan'):
					$kel = 'P';
				endif;
				$data = array(
				'sk_member'=>$row['member_id'],
				'jenis_kelamin'=>$kel,
				'jenis_kelamin_desc'=>$row['jenkel'],
				'nama'=>$row['nama'],
				'kode_member'=>$row['member_id']
				);
				$this->load->database('db_datawarehouse', TRUE); // extrem change db ke second_db nya dbdemankcomp_dw di loop
				$this->Ekstrak_model->loadTable('dim_member',$data);
				$this->load->database('default', TRUE); // extrem change db balik ke db default and ikut di loop
			endforeach;
			$this->load->database('db_datawarehouse', TRUE); // extrem change db ke second_db nya dbdemankcomp_dw
			
			// for dim_time
			
			$this->default_db = $this->load->database('default', TRUE);// extrem change db ke default db nya dbdemankcomp
			$query = $this->default_db->query(
			'SELECT * FROM tb_time');
			$res = $query->result_array(); 	 	 	 	 	
			foreach ($res as $row):
				$data = array(
				'date_sk'=>$row['time_id'],
				'month'=>$row['month'],
				'day'=>$row['day'],
				'year'=>$row['year'],
				'date'=>$row['date'],
				'quarter'=>$row['quarter']
				);
				$this->load->database('db_datawarehouse', TRUE); // extrem change db ke second_db nya dbdemankcomp_dw di loop
				$this->Ekstrak_model->loadTable('dim_time',$data);
				$this->load->database('default', TRUE); // extrem change db balik ke db default and ikut di loop
			endforeach;
			$this->load->database('db_datawarehouse', TRUE); // extrem change db ke second_db nya dbdemankcomp_dw
					
			
				
			// for fact_sales
			$this->default_db = $this->load->database('default', TRUE);// extrem change db ke default db nya dbdemankcomp
			$query = $this->default_db->query(
			'SELECT td.produk_id,o.order_kab_id,o.order_date,o.date_sk,o.order_member_id,o.order_bank_id,td.det_jml,p.harga_dist,p.harga_jual,p.diskon,td.harga,td.det_subtotal
							FROM tb_order_detail td
							LEFT OUTER JOIN tb_produk p ON p.produk_id = td.produk_id
							LEFT OUTER JOIN tb_order o ON o.order_id = td.order_id
							LEFT OUTER JOIN tb_member m ON m.member_id = o.order_member_id
							WHERE o.order_status = "closed"
							GROUP BY td.order_detail_id
							ORDER BY  td.order_detail_id ASC
							');
			$res = $query->result_array();
			foreach ($res as $row):
				$total_dist = ($row['det_jml'] * $row['harga_dist']);
				$diskon = (($row['diskon'] * $row['harga_jual']/100)*$row['det_jml']);
				$y = explode('-',$row['order_date']);
				$year = $y[0];
				$this->load->database('db_datawarehouse', TRUE); // extrem change db ke second_db nya dbdemankcomp_dw di loop
						$data = array(
						'sk_produk'=>$row['produk_id'],
						'sk_lokasi'=>$row['order_kab_id'],
						'sk_member'=>$row['order_member_id'],
						'sk_bank'=>$row['order_bank_id'],
						'date_sk'=>$row['date_sk'],
						'year'=>$year,
						'jumlah_pembelian'=>$row['det_jml'],
						'harga_produk'=>$row['harga'],
						'total_distributor'=>$total_dist,
						'diskon'=>$diskon,
						'total_kotor'=>$row['det_subtotal'],
						);
						$this->load->database('db_datawarehouse', TRUE); // extrem change db ke second_db nya dbdemankcomp_dw di loop
						$this->Ekstrak_model->loadTable('fact_sales',$data);
						$this->load->database('default', TRUE); // extrem change db balik ke db default and ikut di loop
			endforeach;
			$this->load->database('db_datawarehouse', TRUE); // extrem change db ke second_db nya dbdemankcomp_dw
			
			// bikin log
			$dim = $this->uri->segment(4);
			$user = $this->session->userdata('user_display_name');
			$date = date("Y-m-d h:i:s");
			
			$data = array(
								'log_user'=>$user,
								'log_desc'=>'Load datawarehouse semua tahun',
								'log_date'=>$date
								);
			$this->Ekstrak_model->addLog($data);
			$this->session->set_flashdata('message_type','<div id="kotak" class="success">Semua tabel berhasil di load</div>');
			redirect('backend/ekstrak');	
		}
		
		else
		{
			$this->Ekstrak_model->cleanTable('dim_bank');
			$this->Ekstrak_model->cleanTable('dim_lokasi');
			$this->Ekstrak_model->cleanTable('dim_time');
			$this->Ekstrak_model->cleanTable('dim_member');
			$this->Ekstrak_model->cleanTable('dim_produk');
			$this->Ekstrak_model->cleanTable('fact_sales');
			
			// for dim_bank
			$default_db ='';
			$this->default_db = $this->load->database('default', TRUE);// extrem change db ke default db nya dbdemankcomp
			$query = $this->default_db->get('tb_bank');
			$res = $query->result_array();
			foreach ($res as $row):
				$data = array(
				'sk_bank'=>$row['bank_id'],
				'kode_bank'=>$row['bank_id'],
				'nama_bank'=>$row['nama_bank'],
				);
				$this->load->database('db_datawarehouse', TRUE); // extrem change db ke second_db nya dbdemankcomp_dw di loop
				$this->Ekstrak_model->loadTable('dim_bank',$data);
				$this->load->database('default', TRUE); // extrem change db balik ke db default and ikut di loop
			endforeach;
			$this->load->database('db_datawarehouse', TRUE); // extrem change db ke second_db nya dbdemankcomp_dw
			
			
			// for dim_produk
			$this->default_db = $this->load->database('default', TRUE);// extrem change db ke default db nya dbdemankcomp
			$query = $this->default_db->query(
			'SELECT p.produk_id,p.prod_kat_id,k.nama_kategori,p.prod_vendor_id,v.nama_vendor,p.produk_id,p.nama_produk
						FROM tb_produk p
						JOIN tb_kat_produk k ON p.prod_kat_id = k.kategori_id
						JOIN tb_vendor v ON p.prod_vendor_id = v.vendor_id');
			$res = $query->result_array();
			foreach ($res as $row):
				$data = array(
				'sk_produk'=>$row['produk_id'],
				'kode_kategori'=>$row['prod_kat_id'],
				'nama_kategori'=>$row['nama_kategori'],
				'kode_vendor'=>$row['prod_vendor_id'],
				'nama_vendor'=>$row['nama_vendor'],
				'kode_produk'=>$row['produk_id'],
				'nama_produk'=>$row['nama_produk'],
				);
				$this->load->database('db_datawarehouse', TRUE); // extrem change db ke second_db nya dbdemankcomp_dw di loop
				$this->Ekstrak_model->loadTable('dim_produk',$data);
				$this->load->database('default', TRUE); // extrem change db balik ke db default and ikut di loop
			endforeach;
			$this->load->database('db_datawarehouse', TRUE); // extrem change db ke second_db nya dbdemankcomp_dw
			
			//for dim_lokasi
			
			$this->default_db = $this->load->database('default', TRUE);// extrem change db ke default db nya dbdemankcomp
			$query = $this->default_db->query(
			'SELECT k.kab_id,p.province_id,k.kab_name,k.kab_prov_id,p.province_name
						FROM tb_province p
						JOIN tb_kab k ON k.kab_prov_id = p.province_id
						ORDER BY k.kab_id ASC
						');
			$res = $query->result_array();
			foreach ($res as $row):
				$data = array(
				'sk_lokasi'=>$row['kab_id'],
				'kode_propinsi'=>$row['province_id'],
				'nama_propinsi'=>$row['province_name'],
				'kode_kab'=>$row['kab_id'],
				'nama_kab'=>$row['kab_name']
				);
				$this->load->database('db_datawarehouse', TRUE); // extrem change db ke second_db nya dbdemankcomp_dw di loop
				$this->Ekstrak_model->loadTable('dim_lokasi',$data);
				$this->load->database('default', TRUE); // extrem change db balik ke db default and ikut di loop
			endforeach;
			$this->load->database('db_datawarehouse', TRUE); // extrem change db ke second_db nya dbdemankcomp_dw
			
			// for dim_member
			
			$this->default_db = $this->load->database('default', TRUE);// extrem change db ke default db nya dbdemankcomp
			$query = $this->default_db->query('
					SELECT member_id,mem_kab_id,jenkel,nama
					FROM tb_member 
						');
			$res = $query->result_array();
			foreach ($res as $row):
				if ($row['jenkel'] == 'Laki-laki'):
					$kel = 'L';
				elseif($row['jenkel'] == 'Perempuan'):
					$kel = 'P';
				endif;
				$data = array(
				'sk_member'=>$row['member_id'],
				'jenis_kelamin'=>$kel,
				'jenis_kelamin_desc'=>$row['jenkel'],
				'nama'=>$row['nama'],
				'kode_member'=>$row['member_id']
				);
				$this->load->database('db_datawarehouse', TRUE); // extrem change db ke second_db nya dbdemankcomp_dw di loop
				$this->Ekstrak_model->loadTable('dim_member',$data);
				$this->load->database('default', TRUE); // extrem change db balik ke db default and ikut di loop
			endforeach;
			$this->load->database('db_datawarehouse', TRUE); // extrem change db ke second_db nya dbdemankcomp_dw
			
			// for dim_time
			
			$this->default_db = $this->load->database('default', TRUE);// extrem change db ke default db nya dbdemankcomp
			$query = $this->default_db->query(
			'SELECT * FROM tb_time WHERE year = '.$th);
			$res = $query->result_array(); 	 	 	 	 	
			foreach ($res as $row):
				$data = array(
				'date_sk'=>$row['time_id'],
				'month'=>$row['month'],
				'day'=>$row['day'],
				'year'=>$row['year'],
				'date'=>$row['date'],
				'quarter'=>$row['quarter']
				);
				$this->load->database('db_datawarehouse', TRUE); // extrem change db ke second_db nya dbdemankcomp_dw di loop
				$this->Ekstrak_model->loadTable('dim_time',$data);
				$this->load->database('default', TRUE); // extrem change db balik ke db default and ikut di loop
			endforeach;
			$this->load->database('db_datawarehouse', TRUE); // extrem change db ke second_db nya dbdemankcomp_dw
					
			
				
			// for fact_sales
			$this->default_db = $this->load->database('default', TRUE);// extrem change db ke default db nya dbdemankcomp
			$query = $this->default_db->query(
			'SELECT td.produk_id,o.order_kab_id,o.order_date,o.date_sk,o.order_member_id,o.order_bank_id,td.det_jml,p.harga_dist,p.harga_jual,p.diskon,td.harga,td.det_subtotal
							FROM tb_order_detail td
							LEFT OUTER JOIN tb_produk p ON p.produk_id = td.produk_id
							LEFT OUTER JOIN tb_order o ON o.order_id = td.order_id
							LEFT OUTER JOIN tb_member m ON m.member_id = o.order_member_id
							JOIN tb_time tm on tm.time_id = o.date_sk
							WHERE o.order_status = "closed" AND tm.year = '.$th.'
							GROUP BY td.order_detail_id
							ORDER BY  td.order_detail_id ASC
							');
			$res = $query->result_array();
			foreach ($res as $row):
				$total_dist = ($row['det_jml'] * $row['harga_dist']);
				$diskon = (($row['diskon'] * $row['harga_jual']/100)*$row['det_jml']);
				$y = explode('-',$row['order_date']);
				$year = $y[0];
				$this->load->database('db_datawarehouse', TRUE); // extrem change db ke second_db nya dbdemankcomp_dw di loop
						$data = array(
						'sk_produk'=>$row['produk_id'],
						'sk_lokasi'=>$row['order_kab_id'],
						'sk_member'=>$row['order_member_id'],
						'sk_bank'=>$row['order_bank_id'],
						'date_sk'=>$row['date_sk'],
						'year'=>$year,
						'jumlah_pembelian'=>$row['det_jml'],
						'harga_produk'=>$row['harga'],
						'total_distributor'=>$total_dist,
						'diskon'=>$diskon,
						'total_kotor'=>$row['det_subtotal'],
						);
						$this->load->database('db_datawarehouse', TRUE); // extrem change db ke second_db nya dbdemankcomp_dw di loop
						$this->Ekstrak_model->loadTable('fact_sales',$data);
						$this->load->database('default', TRUE); // extrem change db balik ke db default and ikut di loop
			endforeach;
			$this->load->database('db_datawarehouse', TRUE); // extrem change db ke second_db nya dbdemankcomp_dw
			
			// bikin log
			$dim = $this->uri->segment(4);
			$user = $this->session->userdata('user_display_name');
			$date = date("Y-m-d h:i:s");
			
			$data = array(
								'log_user'=>$user,
								'log_desc'=>'Load datawarehouse tahun '.$th,
								'log_date'=>$date
								);
			$this->Ekstrak_model->addLog($data);
			$this->session->set_flashdata('message_type','<div id="kotak" class="success">Semua tabel berhasil di load</div>');
			redirect('backend/ekstrak');	
		}
				
		
	}
	
	
	function clean_all()
	{
		$dim = $this->uri->segment(4);
		$user = $this->session->userdata('user_display_name');
		$date = date("Y-m-d h:i:s");
		
		$data = array(
							'log_user'=>$user,
							'log_desc'=>'Clean all table datawarehouse',
							'log_date'=>$date
							);
		$this->Ekstrak_model->addLog($data);
		
		$this->Ekstrak_model->cleanTable('dim_bank');
		$this->Ekstrak_model->cleanTable('dim_lokasi');
		$this->Ekstrak_model->cleanTable('dim_time');
		$this->Ekstrak_model->cleanTable('dim_member');
		$this->Ekstrak_model->cleanTable('dim_produk');
		$this->Ekstrak_model->cleanTable('fact_sales');
		$this->session->set_flashdata('message_type','<div id="kotak" class="success">Semua tabel berhasil dibersihkan</div>');
		redirect('backend/ekstrak');
	
	}
	
	function backup()
	
	{
		$th = $this->input->post('tahun');
		
		$this->Ekstrak_model->backupDb($th);
		
		redirect('backend/ekstrak/advanced','refresh');
		
	}
	
	function import()
	{
		$error = FALSE;
		$upload_dir = $this->config->item('upload_path');
		if (!$error && isset($_REQUEST["uploadbutton"]))
			{ 
			if (is_uploaded_file($_FILES["sql_file"]["tmp_name"]) && ($_FILES["sql_file"]["error"])==0)
			  { 
				$uploaded_filename=str_replace(" ","_",$_FILES["sql_file"]["name"]);
				$uploaded_filename=preg_replace("/[^_A-Za-z0-9-\.]/i",'',$uploaded_filename);
				$uploaded_filepath=str_replace("\\","",$upload_dir."".$uploaded_filename);

				if (!preg_match("/(\.(sql|gz|csv))$/i",$uploaded_filename))
				{ echo ("<p class=\"error\">You may only upload .sql .gz or .csv files.</p>\n");
				}
				else if (!@move_uploaded_file($_FILES["sql_file"]["tmp_name"],$uploaded_filepath))
				{ echo ("<p class=\"error\">Error moving uploaded file ".$_FILES["sql_file"]["tmp_name"]." to the $uploaded_filepath</p>\n");
				  echo ("<p>Check the directory permissions for $upload_dir (must be 777)!</p>\n");
				}
				else
				{ 
					//echo ("<p class=\"success\">Uploaded file saved as $uploaded_filename</p>\n");
					$file = $uploaded_filepath;
					$this->Ekstrak_model->importDb($file);
					$user = $this->session->userdata('user_display_name');
					$date = date("Y-m-d h:i:s");
					$data = array(
										'log_user'=>$user,
										'log_desc'=>'Import datawarehose db '.$uploaded_filename,
										'log_date'=>$date
										);
					$this->Ekstrak_model->addLog($data);
					redirect('backend/ekstrak/advanced','refresh');
				}
			  }
			  else
			  { echo ("<p class=\"error\">Error uploading file ".$_FILES["sql_file"]["name"]."</p>\n");
			  }

		}
		
		
	}

	function go_olap()
	{
		$cek_bank = $this->db->get('dim_bank')->num_rows();	
		$cek_member = $this->db->get('dim_member')->num_rows();	
		$cek_produk = $this->db->get('dim_produk')->num_rows();	
		$cek_time = $this->db->get('dim_time')->num_rows();	
		$cek_lok = $this->db->get('dim_lokasi')->num_rows();	
		$cek_fact = $this->db->get('fact_sales')->num_rows();	

		if (($cek_bank != 0) && ($cek_member != 0) && ($cek_produk != 0) && ($cek_time != 0) && ($cek_lok != 0) && ($cek_fact != 0) )
		{
			$data['title'] = 'First Check OLAP';
			$data['template'] = 'ekstrak/go_olap';
			$this->load->view('backend/index',$data);
			
		}
		else
		{
			$data['title'] = 'First Check OLAP';
			$data['template'] = 'ekstrak/error_cek';
			$this->load->view('backend/index',$data);
		}
		
	}

}